/*
  # Create system settings table

  1. New Tables
    - `system_settings`
      - `id` (uuid, primary key)
      - `key` (text, unique)
      - `value` (text)
      - `description` (text)
      - `created_at` (timestamp)
      - `updated_at` (timestamp)
      - `user_id` (uuid, foreign key)

  2. Security
    - Enable RLS
    - Add policies for authenticated users
*/

CREATE TABLE IF NOT EXISTS system_settings (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  key text NOT NULL UNIQUE,
  value text,
  description text,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now(),
  user_id uuid REFERENCES auth.users(id) ON DELETE CASCADE
);

-- Enable RLS
ALTER TABLE system_settings ENABLE ROW LEVEL SECURITY;

-- Drop existing policies if any
DROP POLICY IF EXISTS "Users can view system settings" ON system_settings;
DROP POLICY IF EXISTS "Users can create system settings" ON system_settings;
DROP POLICY IF EXISTS "Users can update system settings" ON system_settings;
DROP POLICY IF EXISTS "Users can delete system settings" ON system_settings;

-- Create policies
CREATE POLICY "Users can view system settings"
  ON system_settings
  FOR SELECT
  TO authenticated
  USING (true);

CREATE POLICY "Users can create system settings"
  ON system_settings
  FOR INSERT
  TO authenticated
  WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users can update system settings"
  ON system_settings
  FOR UPDATE
  TO authenticated
  USING (auth.uid() = user_id)
  WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users can delete system settings"
  ON system_settings
  FOR DELETE
  TO authenticated
  USING (auth.uid() = user_id);

-- Create indexes
CREATE INDEX IF NOT EXISTS system_settings_key_idx ON system_settings(key);
CREATE INDEX IF NOT EXISTS system_settings_user_id_idx ON system_settings(user_id);
CREATE INDEX IF NOT EXISTS system_settings_created_at_idx ON system_settings(created_at DESC);
CREATE INDEX IF NOT EXISTS system_settings_updated_at_idx ON system_settings(updated_at DESC);

-- Drop trigger if exists
DROP TRIGGER IF EXISTS update_system_settings_updated_at ON system_settings;

-- Create updated_at trigger
CREATE OR REPLACE FUNCTION update_system_settings_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_system_settings_updated_at
  BEFORE UPDATE ON system_settings
  FOR EACH ROW
  EXECUTE FUNCTION update_system_settings_updated_at(); 